using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;
using System.Web.Script.Serialization;
using System.Text;

public partial class QueryBuilder : System.Web.UI.Page
{
    string strPagesetRowID = "fa94b4929612d0bb"; // ps_report_sql
    DataSet dseInputInfo = new DataSet();
    DataSet dsePageInfo = new DataSet();
    DataSet dsObjAtt = new DataSet();
    DataTable dtQueryExp = new DataTable();
    HtmlForm frmObj;
    security hdds;

    protected void Page_PreInit(object sender, EventArgs e)
    {
        this.Theme = Session["theme"].ToString();
        this.Title = "MaxBlox";
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            hdds = new security();
            if (Session["userid"] == null) Response.Redirect(hdds.SessionExpiryURL);
            if (Session["userid"].ToString() == "") Response.Redirect(hdds.SessionExpiryURL);

            string strErrorMsg = "";
            if (!hdds.IsValidPageURL(ref strErrorMsg))
            {
                lblMessage.Text = wrapItAsMessage(strErrorMsg, true);
                TABLE1.Visible = false;
                return;
            }

            if (Request.QueryString["mn"] != null)
                hdds.logMenuClick(Request.QueryString["mn"].ToString());

            if (this.Master.FindControl("Form1") != null)
                frmObj = (HtmlForm)this.Master.FindControl("Form1");

            PageInfoLoad PIL = new PageInfoLoad();
            dsePageInfo = PIL.GetPageSetPageCtrlInfo(strPagesetRowID);

            if (!IsPostBack)
            {
                loadObjectList();
                loadOwneridDropDown();
                //getDefaultObj();
                CheckForChangeOwner();
                checkEditOperation();
                getpageicons();
                p_sy_report_sql_report_base_name.Focus();
            }
            if (IsPostBack)
            {
                getRelatedColumns();
                getQueryExpression();
                getQuery();
            }
            AddPageToolTip(dsePageInfo.Tables["Page"].Rows[0], ref lblHeader);
            isViewMode();
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Query Builder: Error : Page_Load : " + ex.Message);
        }
    }
    
    private void isViewMode()
    {
        if (HttpContext.Current.Request.QueryString["VMODE"] != null)
            if (HttpContext.Current.Request.QueryString["VMODE"] == "1")
            {
                DynamicDisplayI ddi = new DynamicDisplayI();
                HtmlTable ht = (HtmlTable)frmObj.FindControl("ContentPlaceHolder3").FindControl("TABLE1");
                ddi.disableControls(ht);
                ddi = null;
                cblColumns.Enabled = false;
                p_sy_report_sql_list_object.Enabled = false;
                pnl_Sql_Expression.Enabled = false;
                pnlChbxColumn.Enabled = false;
            }
    }

    #region Load Objects

    private void AddDefaultObjects(ref DataTable dtInput) // Added by 'Imran Gani' on 15-Oct-2013, to handle configured objects
    {
        HDDatabase HDD = new HDDatabase();
        try
        {
            string objectids = "";
            if (dtInput != null)
            {
                foreach (DataRow dr in dtInput.Rows)
                {
                    objectids += "'" + dr["object_id"].ToString() + "',";
                }
                if (objectids.EndsWith(","))
                    objectids = objectids.TrimEnd(',');
            }
            string strSQL = "";
            if (objectids.Trim() == "")
                strSQL = "select mem_id,mem_name,mem_value2 from sy_acr_mem where acr_id = '1b8bcdd101823723' and mem_value1 = 'true' order by mem_name";
            else
                strSQL = "select mem_id,mem_name,mem_value2 from sy_acr_mem where acr_id = '1b8bcdd101823723' and mem_value1 = 'true' and mem_value1 not in (" + objectids + ") order by mem_name";
            DataTable dt = HDD.GetDataTable(strSQL);

            if (dt != null)
                if (dt.Rows.Count > 0)
                    foreach (DataRow dr in dt.Rows)
                    {
                        DataRow drInput = dtInput.NewRow();
                        drInput["object_name"] = dr["mem_name"].ToString();
                        drInput["object_id"] = dr["mem_id"].ToString();
                        drInput["af_row_id"] = dr["mem_value2"].ToString();
                        dtInput.Rows.Add(drInput);
                    }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : AddDefaultObjects : " + ex.Message);
        }
        finally
        {
            HDD = null;
        }
    }

    private void loadObjectList()
    {
        HDDatabase hd = new HDDatabase();
        DataTable dt = new DataTable();
        try
        {
            string Sql = applysecurity("Select * from sy_obj");
            dt = hd.GetDataTable(Sql);

            AddDefaultObjects(ref dt); // Added by 'Imran Gani' on 15-Oct-2013, to handle configured objects

            p_sy_report_sql_primary_object_id.Items.Clear();
            p_sy_report_sql_primary_object_id.Items.Add(new ListItem("--Select a Object--", ""));
            p_sy_report_sql_list_object.Items.Clear();
            //p_sy_report_sql_list_object.Items.Add(new ListItem("", "none"));

            foreach (DataRow dr in dt.Select("", "object_name asc"))
            {
                if (hdds.getObjectRightsForUser(dr["object_id"].ToString(), "read"))
                {
                    ListItem liObject = new ListItem(dr["object_name"].ToString(), dr["af_row_id"].ToString());
                    p_sy_report_sql_primary_object_id.Items.Add(liObject);
                    p_sy_report_sql_list_object.Items.Add(liObject);
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("loadObjectList : Error : " + ex.Message);
        }
        finally
        {
            dt.Dispose();
        }
    }
    private string applysecurity(string sql)
    {
        try
        {
            SecurityFilter objSec = new SecurityFilter();
            string sAFAdmin = objSec.QueryForAFAdmin();
            string sCustodianFilter = objSec.QueryForCustodianFilter();
            if (sCustodianFilter.Trim() != "")
                if (!sCustodianFilter.StartsWith("or"))
                    sAFAdmin += " and " + sCustodianFilter;
                else
                    sAFAdmin = "(" + sAFAdmin + " " + sCustodianFilter + ") ";

            if (sql.Contains("order by"))
            {
                if (sAFAdmin.Trim() != "")
                    sql = sql.Replace("order by", "where " + sAFAdmin + " order by");
            }
            else
            {
                if (sAFAdmin.Trim() != "")
                    sql = sql + " where " + sAFAdmin;
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Query Builder: applysecurity : Error : " + ex.Message);
            return sql;
        }
        return sql;
    }
    #endregion

    #region Load Owner field
    private void loadOwneridDropDown() 
    {
        HDDatabase Hdd = new HDDatabase();
        BindControl BC = new BindControl();
        try
        {
            if (Request.QueryString["PK"] == null)
            {
                //p_sy_report_sql_owner_id_dd.SelectedValue = Session["user_afrowid"].ToString();
                p_sy_report_sql_owner_id.Value = Session["user_afrowid"].ToString();
                txt_p_sy_report_sql_owner_id.Text = Session["userid"].ToString();
            }
            if (Request.QueryString["PK"] != null)
            {
                if (Request.QueryString["CPY"] != null)
                {
                    p_sy_report_sql_owner_id.Value = Session["user_afrowid"].ToString();
                    txt_p_sy_report_sql_owner_id.Text = Session["userid"].ToString();
                }
            }
            //p_sy_report_sql_owner_id.Items.Add(new ListItem(Session["user_afrowid"].ToString(), Session["user_afrowid"].ToString()));
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Query Builder: loadOwneridDropDown : Error : " + ex.Message);
        }
        finally
        {
            Hdd = null;
            BC = null;
        }
    }

    private void CheckForChangeOwner()
    {
        try
        {
            security sec = new security();
            bool status = false;
            status = sec.getRightsForMRUObjects(strPagesetRowID, "change_owner");
            if (status == false)
            {
                txt_p_sy_report_sql_owner_id.Enabled = false;
                imgOwnerSearch.Visible = false;
            }
            sec = null;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Query Builder: CheckForChangeOwner : Error : " + ex.Message);
        }
    }
    #endregion

    private bool checkEditOperation()
    {
        bool bEdit = false;
        try
        {
            if (!IsPostBack)
            {
                if (Request.QueryString["PK"] != null)
                {
                    lblHeader.Text = " Query Designer - Edit";
                    bEdit = true;
                    if (getData())
                        setData(bEdit);
                }
                if (Request.QueryString["CPY"] != null)
                {
                    if (Request.QueryString["CPY"] == "0")
                    {
                        lblHeader.Text = " Query Designer - Copy";
                        bEdit = false;
                    }
                }
                if (bEdit)
                {
                    btnSave.Visible = false;
                    btnUpdate.Visible = true;
                }
                else
                {
                    btnUpdate.Visible = false;
                    btnSave.Visible = true;
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Query Builder: checkEditOperation : Error : " + ex.Message);
            return true;
        }
        return true;
    }

    #region Page Icon and Tooltip
    private void getpageicons()
    {
        PageIcons icons = new PageIcons();
        HDDatabase Hdd = new HDDatabase();
        try
        {
            string strPagesetRowID = Request.QueryString["pgs_id"].ToString();
            lblHeader.Text = "&nbsp;" + icons.geticontextfrompageset(strPagesetRowID, 2) + "&nbsp;" + lblHeader.Text;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error :getpageicons() " + ex.Message);
            lblHeader.Text = "<img src=" + PageIcons.strdefaulticon.ToString() + "></img>&nbsp;" + lblHeader.Text;
        }
        finally
        {
            icons = null;
            Hdd = null;
        }
    }

    private void AddPageToolTip(DataRow drpage, ref Label lblHeader)
    {
        if (drpage != null)
        {
            if (!Page.IsPostBack)
                lblHeader.Text = lblHeader.Text + getTootipIcon(drpage);
            Literal lit = new Literal();
            lit.Text = getTooltipContent(drpage);
            td_Page_ToopTip.Controls.Add(lit);
        }
    }

    private string getTootipIcon(DataRow droPage)
    {
        try
        {
            if (!droPage.Table.Columns.Contains("page_desc")) return "";
            if (droPage["page_desc"].ToString().Trim() == "") return "";
            return "&nbsp;<img id=\"page_tooltip_img\" style=\"cursor:pointer;\" src=\"Images/collapse3.gif\" onClick=\"javascript:showHideTooltipDIV();\" border=\"0\"></img>";
        }
        catch (Exception e)
        {
            return "";
        }
    }

    private string getTooltipContent(DataRow droPage)
    {
        if (!droPage.Table.Columns.Contains("page_desc")) return "";
        if (droPage["page_desc"].ToString().Trim() == "") return "";
        return getContent(HttpContext.Current.Server.MapPath("") + @"\scripts\pagetooltipcontent.txt").Replace("<<content>>", CommonFunctions.SafeEncode(droPage["page_desc"].ToString()));
    }

    private string getContent(string fileName)
    {
        try
        {
            if (!File.Exists(fileName))
                return "";
            string temp = "";
            StreamReader sr = new StreamReader(fileName);
            temp = sr.ReadToEnd();
            sr.Close();
            sr = null;
            return temp;
        }
        catch (Exception e)
        {
            return "";
        }
    }

    private string wrapItAsMessage(string msg, bool isError)
    {
        return "<div class=\"" + (isError == true ? "mbErrorMessage" : "mbMessage") + "\">" + msg + "</div>";
    }
    #endregion

    private bool SaveData()
    {
        bool blnStatus = true;
        ScreenReader SR = new ScreenReader();
        dseInputInfo = SR.CaptureData(strPagesetRowID, frmObj, dsePageInfo, true);
        if (SR.errormessage != "")
        {
            lblMessage.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;" + "Data Insert not successful. Error: " + HttpUtility.HtmlEncode(SR.errormessage), true); ;
            return false;
        }
        //function added by Sampath on 17-Nov-2011 for IPP Tech check 
        //commented by Sampath on 27-Apr-2012  for double time calling encode data
        //CommonFunctions.encodeData(dseInputInfo);
        Master MA = new Master();
        blnStatus = MA.ProcessOperation(dsePageInfo, dseInputInfo, "INSERT");
        if (blnStatus == false)
            lblMessage.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;" + "Data Insert not successful. Error: " + HttpUtility.HtmlEncode(MA.ErrorMessage), true);
        else
            lblMessage.Text = wrapItAsMessage("&nbsp;<img src='Images/info.gif' border='0'>&nbsp;Data Inserted Successfully.", false);//changed by prithi on 07-Aug-2008
        return blnStatus;
    }

    private string getRefURL()
    {
        try
        {
            return "DisplayGrid.aspx?pgs_id=" + Request.QueryString["pgs_id"].ToString() + "&mn=" + Request.QueryString["mn"].ToString();
        }
        catch
        {
            return "";
        }
    }

    private string getSQLId()
    {
        string sSQLId = "1";
        HDDatabase hdd = new HDDatabase();
        try
        {
            string sTemp = hdd.GetColumnValue("Select max(convert(int,report_base_id)) from sy_report_sql");
            if (sTemp != "")
                sSQLId = Convert.ToString(Convert.ToInt32(sTemp) + 1);
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("getSQLId : Error : " + ex.Message);
            return "1";
        }
        return sSQLId;
    }

    private void disableControls()
    {
        p_sy_report_sql_report_base_name.Enabled = false;
        p_sy_report_sql_report_base_desc.Enabled = false;
        p_sy_report_sql_report_base_sql.Enabled = false;
        btnSave.Enabled = false;
        btnUpdate.Enabled = false;
    }

    private bool getData()
    {
        bool bReturn = true;
        try
        {
            HDDatabase hdd = new HDDatabase();

            string sPageKey = Request.QueryString["PK"].ToString();
            p_sy_report_sql_af_row_id.Value = sPageKey;
            sPageKey = hdd.GetColumnValue("select report_base_id from sy_report_sql where af_row_id = '" + sPageKey + "'");
            foreach (DataRow dr in dsePageInfo.Tables["page"].Rows)
            {
                string sObject_id = dr["object_id"].ToString();
                if (hdds.getObjectRightsForUser(dr["object_id"].ToString(), "read"))
                {
                    string sSql = "select * from " + dr["page_table_name"].ToString() + " where report_base_id = '" + sPageKey + "'";
                    DataTable dt = hdd.GetDataTable(sSql);
                    dt.TableName = dr["page_id"].ToString();
                    dseInputInfo.Tables.Add(dt.Copy());

                    //function added by Sampath on 17-Nov-2011 for IPP Tech check 
                    //CommonFunctions.decodeData(dseInputInfo);
                }
                else
                {
                    disableControls();
                    bReturn = false;
                }
            }
        }

        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : " + ex.Message);
            return false;
        }
        return bReturn;
    }

    private void setData(bool IsEdit)
    {
        //function added by bala on 18-Nov-2011 for IPP Tech check 
        //CommonFunctions.decodeData(dseInputInfo);
        if (dseInputInfo.Tables["p_sy_report_sql"] != null)
        {
            if (dseInputInfo.Tables["p_sy_report_sql"].Rows[0] != null)
            {
                if (IsEdit)
                {
                    if (Request.QueryString["CPY"] != null)
                    {
                        ScreenReader SR = new ScreenReader();
                        p_sy_report_sql_af_row_id.Value = SR.getHashKey();
                        SR = null;
                        p_sy_report_sql_owner_id_dd.SelectedValue = Session["user_afrowid"].ToString();
                        p_sy_report_sql_owner_id.Value = Session["user_afrowid"].ToString();
                        txt_p_sy_report_sql_owner_id.Text = Session["userid"].ToString();
                    }
                    else
                    {
                        p_sy_report_sql_af_row_id.Value = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["af_row_id"].ToString();
                        p_sy_report_sql_owner_id_dd.SelectedValue = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["owner_id"].ToString();
                        p_sy_report_sql_owner_id.Value = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["owner_id"].ToString();
                        LoadEditPage LEP = new LoadEditPage();
                        txt_p_sy_report_sql_owner_id.Text = LEP.GetUserIDFromAfrowid(dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["owner_id"].ToString());
                        LEP = null;

                    }

                    p_sy_report_sql_report_base_id.Text = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["report_base_id"].ToString();
                    p_sy_report_sql_created_at.Value = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["created_at"].ToString();
                    p_sy_report_sql_created_by.Value = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["created_by"].ToString();
                    // Added by srivatsan on June 15 2009 for owner id field

                }
                else
                {
                    ScreenReader sc = new ScreenReader();
                    p_sy_report_sql_af_row_id.Value = sc.getHashKey();
                    p_sy_report_sql_report_base_id.Text = getSQLId();
                    p_sy_report_sql_owner_id_dd.SelectedValue = Session["userafrowid"].ToString();
                    p_sy_report_sql_owner_id.Value = Session["user_afrowid"].ToString();
                    txt_p_sy_report_sql_owner_id.Text = Session["userid"].ToString();
                }

                p_sy_report_sql_report_base_name.Text = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["report_base_name"].ToString();
                p_sy_report_sql_report_base_desc.Text = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["report_base_desc"].ToString();
                p_sy_report_sql_report_base_sql.Text = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["report_base_sql"].ToString();
                p_sy_report_sql_report_base_param.Text = checkParameterSequence(dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["report_base_param"].ToString());
                // Below line added by 'Imran Gani' on 02/05/12, to get the value for primary object.
                ListItem liObj = p_sy_report_sql_list_object.Items.FindByValue(dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["primary_object_id"].ToString());
                if (liObj != null)
                {
                    //p_sy_report_sql_primary_object_id.SelectedValue = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["primary_object_id"].ToString();
                    p_sy_report_sql_list_object.SelectedValue = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["primary_object_id"].ToString();
                }
                else
                {
                    p_sy_report_sql_primary_object_id.SelectedValue = "";
                    //p_sy_report_sql_list_object.SelectedValue = "none";
                }
                txt_sql_builder.Text = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["report_base_sql"].ToString();
                string strSysQuery = dseInputInfo.Tables["p_sy_report_sql"].Rows[0]["is_system_query"].ToString();
                p_sy_report_sql_is_user_query.Value = strSysQuery;
                p_sy_report_sql_is_system_query.Checked = (strSysQuery == "" ? false : Convert.ToBoolean(strSysQuery));
                if (p_sy_report_sql_is_system_query.Checked && p_sy_report_sql_primary_object_id.SelectedValue != "")
                    loadQueryBuilder();
                else
                {
                    lblColNames.Text = "This Query has been entered manually.";
                    lblColNames.Visible = true;
                }
            }
        }
    }

    protected void btnCheck_Click(object sender, EventArgs e)
    {
        string sReturn = validateSQL();

        if (sReturn == "")
        {
            if (checkSQL())
                lblMessage.Text = wrapItAsMessage("&nbsp;<img src='Images/tick4.gif' border='0'>&nbsp;Query checked successfully. Valid Query.", false);//changed by prithi on 07-Aug-2008
        }
        else
            lblMessage.Text = wrapItAsMessage(sReturn, true);
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        string sReturn = validateSQL();

        if (sReturn == "")
        {
            if (checkSQL())
            {
                checkSystemQuery();
                if (SaveData())
                {
                    SaveQueryBuild();
                    string refURL = getRefURL();
                    if (refURL.Length > 0)
                        Response.Redirect(refURL);
                }
            }
        }
        else
            lblMessage.Text = wrapItAsMessage(sReturn, true);
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        string sReturn = validateSQL();

        if (sReturn == "")
        {
            if (checkSQL())
            {
                checkSystemQuery();
                if (UpdateData())
                {
                    SaveQueryBuild();
                    string refURL = getRefURL();
                    if (refURL.Length > 0)
                        Response.Redirect(refURL);
                }
            }
        }
        else
            lblMessage.Text = wrapItAsMessage(sReturn, true);
    }

    private bool UpdateData()
    {
        bool blnStatus = true;
        ScreenReader SR = new ScreenReader();
        dseInputInfo = SR.CaptureData(strPagesetRowID, frmObj, dsePageInfo, false);
        if (SR.errormessage != "")
        {
            lblMessage.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;" + "Data Update not successful. Error: " + HttpUtility.HtmlEncode(SR.errormessage), true);
            return false;
        }


        Master MA = new Master();
        blnStatus = MA.ProcessOperation(dsePageInfo, dseInputInfo, "UPDATE");
        if (blnStatus == false)
            lblMessage.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;" + "Data Update not successful. Error: " + HttpUtility.HtmlEncode(MA.ErrorMessage), true);
        else
            lblMessage.Text = wrapItAsMessage("&nbsp;<img src='Images/info.gif' border='0'>&nbsp;Data Updated Successfully.", false);//changed by prithi on 07-Aug-2008
        return blnStatus;
    }

    protected void btnReset_Click(object sender, EventArgs e)
    {
        try
        {
            string strPageUrl = Request.Url.ToString();
            Response.Redirect(strPageUrl);
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : Query Builder : btnReset_Click() : " + ex.Message);
        }
    }

    private string validateSQL()
    {
        string sReturn = "";
        try
        {
            if (p_sy_report_sql_report_base_sql.Text.Trim() == "")
                sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Please input a query.";
            else if (!p_sy_report_sql_report_base_sql.Text.Trim().ToLower().Contains("select"))
                sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;The SQL should be a select statement.";
            else if (p_sy_report_sql_report_base_sql.Text.Trim().ToLower().StartsWith("insert"))
                sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;insert statements are not allowed.";
            else if (p_sy_report_sql_report_base_sql.Text.Trim().ToLower().StartsWith("update"))
                sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;update statements are not allowed.";
            else if (p_sy_report_sql_report_base_sql.Text.Trim().ToLower().StartsWith("delete"))
                sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;delete statements are not allowed.";
            else if (p_sy_report_sql_report_base_sql.Text.Trim().ToLower().StartsWith("truncate"))
                sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;truncate statements are not allowed.";
            else if (p_sy_report_sql_report_base_sql.Text.Trim().ToLower().StartsWith("alter"))
                sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;alter statements are not allowed.";
            else if (p_sy_report_sql_report_base_sql.Text.Trim().ToLower().StartsWith("drop"))
                sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;drop statements are not allowed.";
            else if (p_sy_report_sql_report_base_param.Text.Trim() != "")
            {
                string[] tmp = p_sy_report_sql_report_base_param.Text.ToLower().Replace("parameters", "").Trim().Split(',');
                foreach (string st in tmp)
                {
                    if (st.Trim() == "") continue;
                    if (st.IndexOf(".") == -1)
                    {
                        sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Parameters not defined properly.";
                        break;
                    }
                    string parName = string.Empty;
                    string[] sChk = { "][" }; // Added by 'Imran Gani' on 28-Oct-2013, to check parameter with new syntax of sequence.
                    string checkPara = string.Empty;
                    if (st.Split(sChk, StringSplitOptions.None).Length > 2)
                        checkPara = st.Substring(st.IndexOf("][") + 1);
                    else
                        checkPara = st;
                    parName = checkPara.Substring(0, checkPara.IndexOf("]") + 1).Trim();
                    string parName1 = parName;
                    if (!parName.StartsWith("[") || !parName.EndsWith("]"))
                    {
                        sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Parameters not defined properly.";
                        break;
                    }
                    string[] dots = checkPara.Replace(parName, "").Split('.');
                    parName = parName.Replace("[", "$").Replace("]", "$");
                    parName1 = parName1.Replace("[", "#").Replace("]", "#");
                    if (dots.Length < 2)
                    {
                        sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Parameters not defined properly.";
                        break;
                    }
                    if (!(dots[0].Trim().StartsWith("[") && dots[1].Trim().Contains("]")))
                    {
                        sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Parameters not defined properly.";
                        break;
                    }
                    if ((dots[0].Trim().EndsWith("]") && dots[1].Trim().StartsWith("[")))
                    {
                        sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Parameters not defined properly.";
                        break;
                    }
                    if (p_sy_report_sql_report_base_sql.Text.ToLower().IndexOf(parName.ToLower()) == -1)
                    {
                        if (p_sy_report_sql_report_base_sql.Text.ToLower().IndexOf(parName1.ToLower()) == -1)
                        {
                            sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Parameters not used in the SQL Query.";
                            break;
                        }
                    }
                }
            }
            else
            {
                if (p_sy_report_sql_report_base_sql.Text.ToLower().Contains("from"))
                {
                    #region MyRegion
                    //int iStartPos = 0;
                    //int iEndPos = 0;

                    //iStartPos = TextBox1.Text.Trim().IndexOf(" from ") + " from ".Length;
                    //iEndPos = TextBox1.Text.Trim().IndexOf(" ", iStartPos);

                    //if (iEndPos == -1)
                    //    iEndPos = TextBox1.Text.Trim().Length;

                    //string sObject_Name = TextBox1.Text.Trim().Substring(iStartPos, iEndPos - iStartPos); 
                    #endregion
                }
                else
                    sReturn = "<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;The query contains no 'from' clause. Please check the syntax.";
            }
        }
        catch (Exception ex)
        {
            return ex.Message;
        }
        return sReturn;
    }

    private bool checkSQL()
    {
        SqlConnection objConn = null;
        SqlCommand objCmd = null;

        if (!p_sy_report_sql_report_base_param.Text.Trim().ToLower().StartsWith("parameters") && p_sy_report_sql_report_base_param.Text.Trim() != "")
            p_sy_report_sql_report_base_param.Text = "parameters " + p_sy_report_sql_report_base_param.Text;

        string query = p_sy_report_sql_report_base_sql.Text.Trim();//p_sy_report_sql_report_base_param.Text.Trim() + ";" + 

        try
        {
            //No $,#,PARAMETERS - regular query
            //modified by Sampath on 03/02/10
            if (!query.Contains("$") && !query.Contains("#") && p_sy_report_sql_report_base_param.Text.Trim() == "")
            {
                lblMessage.Text = "";
                objConn = new SqlConnection(Session["dbcon"].ToString());
                objConn.Open();
                objCmd = new SqlCommand(query, objConn);
                int iResult = objCmd.ExecuteNonQuery();
                objConn.Close();
                objCmd.Dispose();
                objConn.Dispose();
                objCmd = null;
                objConn = null;
            }
            //$,# - No PARAMETERS - parameterized query with old design
            else if ((query.Contains("$") && query.Contains("#")) && p_sy_report_sql_report_base_param.Text.Trim() == "")
            {
                if (!checkVariables("$")) return false;
                if (!checkVariables("#")) return false;
                Hashtable hsParam = new Hashtable();
                getParameters("$", ref hsParam);
                getParameters("#", ref hsParam);
                string sSql = query;

                foreach (string sParam in hsParam.Keys)
                    sSql = sSql.Replace(sParam, "");

                objConn = new SqlConnection(Session["dbcon"].ToString());
                objConn.Open();
                objCmd = new SqlCommand(sSql, objConn);
                int iResult = objCmd.ExecuteNonQuery();
                objConn.Close();
                objCmd.Dispose();
                objConn.Dispose();
                objCmd = null;
                objConn = null;
                hsParam = null;
            }
            //PARAMETERS - no $,# - parameterized query with new design
            //added by Sampath on 03/02/10
            else if (p_sy_report_sql_report_base_param.Text.Trim() != "")
            {
                //Parameter and SQL Query separator is ;
                //if (!checkVariables(";")) return false;
                Hashtable hsParam = new Hashtable();
                string param = p_sy_report_sql_report_base_param.Text;
                //get the variables list from p_sy_report_sql_report_base_param
                if (p_sy_report_sql_report_base_param.Text.ToLower().StartsWith("parameters"))
                    param = p_sy_report_sql_report_base_param.Text.Replace("parameters", "");
                foreach (string str1 in param.Split(','))
                {
                    if (str1.Trim() == "") continue;
                    string sParameter = getParameterKey(str1.Trim()); // Added by 'Imran Gani' on 28-Oct-2013, to get parameter key for hashtable.
                    if (sParameter.IndexOf("=") == -1)
                        hsParam.Add(sParameter.Trim(), getDefaultValue(sParameter.Substring(sParameter.IndexOf("]") + 1).Trim()));
                    else
                        hsParam.Add(sParameter.Substring(0, sParameter.IndexOf("=")).Trim(), sParameter.Substring(sParameter.IndexOf("=") + 1).Trim());
                }

                ReplaceSysVariables(ref hsParam); // Added by 'Imran Gani' on 09-Jul-2012, to replace the system variables with their values.

                foreach (string sParam in hsParam.Keys)
                {
                    string tmpP = "$" + sParam.Substring(0, sParam.LastIndexOf("[")).Replace("[", "").Replace("]", "") + "$";
                    string outVal = checkSingleQuote(tmpP, hsParam[sParam].ToString(), query);
                    query = query.Replace(tmpP, outVal);//hsParamNew[sParam].ToString()

                    tmpP = "#" + sParam.Substring(0, sParam.LastIndexOf("[")).Replace("[", "").Replace("]", "") + "#";
                    outVal = checkSingleQuote(tmpP, hsParam[sParam].ToString(), query);
                    query = query.Replace(tmpP, outVal);//hsParamNew[sParam].ToString()
                }

                objConn = new SqlConnection(Session["dbcon"].ToString());
                objConn.Open();
                objCmd = new SqlCommand(query, objConn);
                int iResult = objCmd.ExecuteNonQuery();
                objConn.Close();
                objCmd.Dispose();
                objConn.Dispose();
                objCmd = null;
                objConn = null;
                hsParam = null;
            }
        }
        catch (SqlException ex)
        {
            lblMessage.Text = wrapItAsMessage("&nbsp;<img src='Images/error.gif' border='0'>&nbsp;" + ex.Message, true);
            return false;
        }

        catch (Exception ex)
        {
            lblMessage.Text = wrapItAsMessage("&nbsp;<img src='Images/error.gif' border='0'>&nbsp;" + ex.Message, true);
            return false;
        }
        finally
        {
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
                objCmd = null;
            }

            if (objCmd != null)
            {
                objCmd.Dispose();
                objConn = null;
            }
        }
        return true;
    }
    private string checkSingleQuote(string parName, string inputVal, string qry)
    {
        string outVal = inputVal;
        try
        {
            if (qry.IndexOf(parName) != -1)
            {
                string tmp = qry.Substring(qry.IndexOf(parName) - 3, 3 + parName.Length);
                if (tmp.Contains("%") || tmp.Contains("'"))
                    outVal = inputVal.TrimStart('\'').TrimEnd('\'');
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("checkSingleQuote:Error:" + ex.Message);
        }
        return outVal;
    }

    private string getDefaultValue(string object_field_id)
    {
        string retValue = "''";
        try
        {
            string object_table = object_field_id.Replace("[", "").Substring(0, object_field_id.Replace("[", "").IndexOf("."));
            string field_id = object_field_id.Substring(object_field_id.IndexOf(".") + 1).Replace("]", "");

            HDDatabase hdd = new HDDatabase();
            DataTable dt = hdd.GetDataTable("select control_attrib_type from sy_pg_ctr where control_id='" + field_id + "' and page_id=(select af_row_id from sy_pg where page_table_name='" + object_table + "')");
            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["control_attrib_type"].ToString().ToUpper() == "TX")
                    retValue = "''";
                else if (dt.Rows[0]["control_attrib_type"].ToString().ToUpper() == "DA" || dt.Rows[0]["control_attrib_type"].ToString().ToUpper() == "DT")
                    retValue = "'" + DateTime.Now.ToShortDateString() + "'";
                else
                    retValue = "0";
            }
            dt = null;
            hdd = null;
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("getDefaultValue : Error : " + ex.Message);
        }
        return retValue;
    }

    private Hashtable getParameters(string sChar, ref Hashtable hsParam)
    {
        try
        {
            string sSql = p_sy_report_sql_report_base_sql.Text.Trim();
            int iPos = 0;

            while (iPos < sSql.Length & iPos != -1)
            {
                string sParam = "";
                iPos = sSql.IndexOf(sChar, iPos);
                if (iPos != -1)
                {
                    int iEndPos = sSql.IndexOf(sChar, iPos + 1);
                    if (iEndPos == -1)
                        iEndPos = sSql.Length - iPos;
                    else
                        iEndPos = iEndPos - (iPos - 1);
                    sParam = sSql.Substring(iPos, iEndPos);
                    sParam = sParam.TrimEnd('\'');
                    if (!hsParam.Contains(sParam))
                        hsParam.Add(sParam, "");
                    iPos = iPos + iEndPos;
                }
            }
        }
        catch (Exception Ex)
        {
            LogWriter.WriteLog("getParameters : Error : " + Ex.Message);
        }
        return hsParam;
    }

    private bool checkVariables(string sChar)
    {
        try
        {
            int iPos = 0;
            string sSql = p_sy_report_sql_report_base_sql.Text.Trim();
            while (iPos < sSql.Length & iPos != -1)
            {
                iPos = sSql.IndexOf(sChar, iPos);
                if (iPos != -1)
                {
                    int iEndPos = sSql.IndexOf(sChar, iPos + 1);
                    if (iEndPos < iPos)
                    {
                        lblMessage.Text = wrapItAsMessage("<img src=\"Images/error.gif\" alt=\"Error\"></img>&nbsp;Please check the syntax", true);
                        return false;
                    }
                    iPos = iEndPos + 1;
                }
            }
        }
        catch (Exception Ex)
        {
            LogWriter.WriteLog("checkVariables : Error : " + Ex.Message);
            return false;
        }
        return true;
    }

    /// <summary>
    /// To replace the system variables with their values
    /// </summary>
    /// <param name="hsParam">Parameter HashTable</param>
    private void ReplaceSysVariables(ref Hashtable hsParam) // Implemented by 'Imran Gani' on 09-Jul-2012
    {
        try
        {
            ArrayList keys = new ArrayList();
            foreach (string de in hsParam.Keys)
                keys.Add(de);

            foreach (string strParam in keys)
            {
                if (hsParam[strParam].ToString().StartsWith("{!") && hsParam[strParam].ToString().EndsWith("}"))
                {
                    Hashtable hsSysVar = new Hashtable();
                    if (HttpContext.Current.Session["sysvar"] == null)
                    {
                        loadSystemVariables(ref hsSysVar);
                        HttpContext.Current.Session["sysvar"] = hsSysVar;
                    }
                    else
                        hsSysVar = (Hashtable)HttpContext.Current.Session["SysVar"];
                    if (hsSysVar.Contains(hsParam[strParam].ToString()))
                        hsParam[strParam] = hsSysVar[hsParam[strParam].ToString()].ToString();
                }
                else if (hsParam[strParam].ToString().StartsWith("{@") && hsParam[strParam].ToString().EndsWith("}"))
                {
                    security hds = new security();
                    Hashtable hsAppVar = new Hashtable();
                    hds.loadAppVariables(ref hsAppVar);
                    hds = null;
                    if (hsAppVar.Contains(hsParam[strParam].ToString()))
                        hsParam[strParam] = hsAppVar[hsParam[strParam].ToString()].ToString();
                }
                else if (hsParam[strParam].ToString() == "CurrentDate()")
                {
                    hsParam[strParam] = DateTime.Now.ToShortDateString();
                }
            }
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in ReportSQL:ReplaceSysVariables: " + e.Message);
        }
        finally
        {
        }
    }

    /// <summary>
    /// To load the system variables.
    /// </summary>
    /// <param name="sysVar">HashTable for system variables</param>
    private void loadSystemVariables(ref Hashtable sysVar) // Implemented by 'Imran Gani' on 09-Jul-2012
    {
        Master objmaster = new Master();
        try
        {
            DataSet ds = new DataSet();
            string dsTemp = "";
            HDDatabase db = new HDDatabase();
            ds = db.GetDataset("select * from sy_var");
            if (ds.Tables.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    switch (dr["sy_var_type"].ToString())
                    {
                        case "DQ"://database query
                            dsTemp = db.GetColumnValue(objmaster.ReplaceVariables(dr["sy_var_value"].ToString(), ref sysVar));

                            if (sysVar.Contains("{!" + dr["sy_var_name"].ToString() + "}"))
                            {
                                if (dsTemp.ToLower() != sysVar["{!" + dr["sy_var_name"].ToString() + "}"].ToString().Trim())
                                {
                                    if (dsTemp.ToString().Trim() != "")
                                    {
                                        sysVar["{!" + dr["sy_var_name"].ToString() + "}"] = dsTemp;
                                    }
                                }
                            }
                            else
                                sysVar.Add("{!" + dr["sy_var_name"].ToString() + "}", dsTemp);
                            break;
                        case "CO"://constant
                            if (dr["sy_var_value"].ToString().Length > 0)
                                sysVar.Add("{!" + dr["sy_var_name"].ToString() + "}", dr["sy_var_value"]);
                            else
                                sysVar.Add("{!" + dr["sy_var_name"].ToString() + "}", dr["sy_var_defaultvalue"]);
                            break;
                    }
                }
            }
            ds = null;
            db = null;
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in ReportSQL:loadSystemVariables: " + e.Message);
        }
        finally
        {
            objmaster = null;
        }
    }
    

    /// <summary>
    /// To get the related columns of the select object.
    /// </summary>
    private void getRelatedColumns()
    {
        HDDatabase hdd = new HDDatabase();
        try
        {
            if (p_sy_report_sql_list_object.SelectedValue != "")
            {
                lblMessage.Text = "";
                lblQExpr.Visible = false;
                pnl_Sql_Expression.Visible = false;
                //p_sy_report_sql_report_base_sql.Text = "";
                dsObjAtt = hdd.GetDataset("select af_row_id, attrib_id, attrib_seq from sy_obj_att where object_id='" + p_sy_report_sql_list_object.SelectedValue + "';");
                if (p_sy_report_sql_primary_object_id.SelectedValue != p_sy_report_sql_list_object.SelectedValue)
                {
                    ViewState["Query_Table"] = "";
                    p_sy_report_sql_is_user_query.Value = "";
                    cblColumns.Items.Clear();
                    foreach (DataRow dr in dsObjAtt.Tables[0].Select("", "attrib_seq asc"))
                    {
                        ListItem liObjAtt = new ListItem(dr["attrib_id"].ToString(), dr["af_row_id"].ToString());
                        cblColumns.Items.Add(liObjAtt);
                    }
                    p_sy_report_sql_primary_object_id.SelectedValue = p_sy_report_sql_list_object.SelectedValue;
                }

                if (ViewState["Query_Table"] == null || ViewState["Query_Table"].ToString() == "" || ViewState["Query_Table"].ToString() == "{\"data\":null}")
                {
                    string sQuery_id = Request.QueryString["PK"] != null ? Request.QueryString["PK"].ToString() : "";
                    dtQueryExp = hdd.GetDataTable("select * from sy_report_sql_setting where query_id = '" + sQuery_id + "' and object_id = '" + p_sy_report_sql_list_object.SelectedValue + "';");
                }
                else
                    dtQueryExp = JsonFunctions.JsonFunctions.JsonToDataTable(ViewState["Query_Table"].ToString());

                for (int i = 0; i < cblColumns.Items.Count; i++)
                {
                    DataRow[] drCheck = dtQueryExp.Select("attrib_id = '" + cblColumns.Items[i].Value + "'");
                    if (cblColumns.Items[i].Selected)
                    {
                        if (drCheck.Length == 0)
                        {
                            DataRow dr1 = dtQueryExp.NewRow();
                            dr1["attrib_id"] = cblColumns.Items[i].Value;
                            dr1["output"] = true;
                            dtQueryExp.Rows.Add(dr1);
                            p_sy_report_sql_is_user_query.Value = "";
                        }
                    }
                    else if (drCheck.Length > 0)
                    {
                        cblColumns.Items.FindByValue(cblColumns.Items[i].Value).Selected = false;
                        dtQueryExp.Rows.Remove(drCheck[0]);
                        p_sy_report_sql_is_user_query.Value = "";
                    }
                }
                ViewState["Query_Table"] = new JavaScriptSerializer().Serialize(JsonFunctions.JsonFunctions.ToJson(dtQueryExp));
                lblColNames.Text = "Select the fields:";
                lblColNames.Visible = true;
                cblColumns.Enabled = true;
                pnlChbxColumn.Visible = true;
                pnlChbxColumn.CssClass = "page_tooltip_div";
            }
            else
            {
                p_sy_report_sql_primary_object_id.SelectedValue = "";
                lblMessage.Text = wrapItAsMessage("Select a valid object.", true);
                lblColNames.Visible = false;
                cblColumns.Enabled = false;
                pnlChbxColumn.Visible = false;
                return;
            }
        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in QueryBuilder: getRelatedColumns: " + ee.Message);
        }
        finally
        {
            hdd = null;
        }
    }

    /// <summary>
    /// To create a dynamic controls for query expression.
    /// </summary>
    private void getQueryExpression()
    {
        HDDatabase hdd = new HDDatabase();
        HtmlTable htTable = new HtmlTable();
        HtmlTableRow htr = new HtmlTableRow();
        HtmlTableCell htc = new HtmlTableCell();
        htTable.CellSpacing = 0;
        htTable.CellPadding = 2;
        try
        {
            if (dtQueryExp.Rows.Count > 0)
            {
                #region QExpression Header
                Label lblOutput = new Label();
                lblOutput.Text = "Output";
                lblOutput.Width = 40;
                htc.Controls.Add(lblOutput);
                htr.Cells.Add(htc);

                htc = new HtmlTableCell();
                Label lblField = new Label();
                lblField.Text = "Field";
                lblField.Width = 150;
                htc.Controls.Add(lblField);
                htr.Cells.Add(htc);

                htc = new HtmlTableCell();
                Label lblAlias = new Label();
                lblAlias.Text = "Alias";
                lblAlias.Width = 100;
                htc.Controls.Add(lblAlias);
                htr.Cells.Add(htc);

                htc = new HtmlTableCell();
                Label lblSortType = new Label();
                lblSortType.Text = "Sort Type";
                lblSortType.Width = 100;
                htc.Controls.Add(lblSortType);
                htr.Cells.Add(htc);

                htc = new HtmlTableCell();
                Label lblExp = new Label();
                lblExp.Text = "Expression";
                lblExp.Width = 100;
                htc.Controls.Add(lblExp);
                htr.Cells.Add(htc);

                htc = new HtmlTableCell();
                Label lblCriteria = new Label();
                lblCriteria.Text = "Criteria";
                lblCriteria.Width = 100;
                htc.Controls.Add(lblCriteria);
                htr.Cells.Add(htc);

                htc = new HtmlTableCell();
                Label lblOr1 = new Label();
                lblOr1.Text = "Or..";
                lblOr1.Width = 100;
                htc.Controls.Add(lblOr1);
                htr.Cells.Add(htc);

                htc = new HtmlTableCell();
                Label lblOr2 = new Label();
                lblOr2.Text = "Or..";
                lblOr2.Width = 100;
                htc.Controls.Add(lblOr2);
                htr.Cells.Add(htc);

                htc = new HtmlTableCell();
                Label lblOr3 = new Label();
                lblOr3.Text = "Or..";
                lblOr3.Width = 100;
                htc.Controls.Add(lblOr3);
                htr.Cells.Add(htc);

                htr.Attributes.Add("class", "Grid_Header");
                htTable.Rows.Add(htr);
                #endregion

                #region QExpression Table
                DataTable dtAggregate = hdd.GetDataTable("select mem_name, mem_id, mem_seq from sy_acr_mem where acr_id='3644df6984de957Q';");
                foreach (DataRow dr in dtQueryExp.Rows)
                {
                    string sColumn_id = dr["attrib_id"].ToString();
                    htr = new HtmlTableRow();
                    htc = new HtmlTableCell();
                    CheckBox cbOutput = new CheckBox();
                    cbOutput.ID = "chb_output_" + sColumn_id;
                    cbOutput.Text = "";
                    cbOutput.Checked = true;
                    cbOutput.AutoPostBack = true;
                    cbOutput.CheckedChanged += new EventHandler(Load_QueryFromExp);
                    htc.Controls.Add(cbOutput);
                    htc.VAlign = "middle";
                    htr.Cells.Add(htc);

                    htc = new HtmlTableCell();
                    DropDownList ddlFld = new DropDownList();
                    ddlFld.ID = "ddl_Fld_" + sColumn_id;
                    ddlFld.CssClass = "controlFont";
                    ddlFld.Width = 150;
                    foreach (DataRow drExp in dsObjAtt.Tables[0].Select("af_row_id = '" + sColumn_id + "'", "attrib_seq asc"))
                    {
                        ListItem liExp = new ListItem(drExp["attrib_id"].ToString(), drExp["af_row_id"].ToString());
                        ddlFld.Items.Add(liExp);
                    }
                    //ddlFld.AutoPostBack = true;
                    ddlFld.SelectedIndexChanged += new EventHandler(Load_QueryFromExp);
                    htc.Controls.Add(ddlFld);
                    htc.VAlign = "middle";
                    htr.Cells.Add(htc);

                    htc = new HtmlTableCell();
                    TextBox txtAlias = new TextBox();
                    txtAlias.ID = "txt_Alias_" + sColumn_id;
                    txtAlias.CssClass = "controlFont";
                    txtAlias.Width = 100;
                    txtAlias.AutoPostBack = true;
                    txtAlias.TextChanged += new EventHandler(Load_QueryFromExp);
                    htc.Controls.Add(txtAlias);
                    htc.VAlign = "middle";
                    htr.Cells.Add(htc);

                    htc = new HtmlTableCell();
                    DropDownList ddlSortType = new DropDownList();
                    ddlSortType.ID = "ddl_sort_type_" + sColumn_id;
                    ddlSortType.CssClass = "controlFont";
                    ddlSortType.Width = 100;
                    ListItem li = new ListItem("", "");
                    ddlSortType.Items.Add(li);
                    li = new ListItem("Ascending", "asc");
                    ddlSortType.Items.Add(li);
                    li = new ListItem("Descending", "desc");
                    ddlSortType.Items.Add(li);
                    ddlSortType.AutoPostBack = true;
                    ddlSortType.SelectedIndexChanged += new EventHandler(Load_QueryFromExp);
                    htc.Controls.Add(ddlSortType);
                    htc.VAlign = "middle";
                    htr.Cells.Add(htc);

                    htc = new HtmlTableCell();
                    DropDownList ddlExp = new DropDownList();
                    ddlExp.ID = "ddl_Exp_" + sColumn_id;
                    ddlExp.CssClass = "controlFont";
                    ddlExp.Width = 100;
                    ListItem liObj = new ListItem("", "");
                    ddlExp.Items.Add(liObj);
                    foreach (DataRow drAggregate in dtAggregate.Select("", "mem_seq asc"))
                    {
                        ListItem liObject = new ListItem(drAggregate["mem_name"].ToString(), drAggregate["mem_id"].ToString());
                        ddlExp.Items.Add(liObject);
                    }
                    ddlExp.AutoPostBack = true;
                    ddlExp.SelectedIndexChanged += new EventHandler(Load_QueryFromExp_Agg);
                    htc.Controls.Add(ddlExp);
                    htc.VAlign = "middle";
                    htr.Cells.Add(htc);

                    htc = new HtmlTableCell();
                    TextBox txtCriteria = new TextBox();
                    txtCriteria.ID = "txt_criteria_" + sColumn_id;
                    txtCriteria.CssClass = "controlFont";
                    txtCriteria.Width = 100;
                    txtCriteria.AutoPostBack = true;
                    txtCriteria.TextChanged += new EventHandler(Load_QueryFromExp);
                    htc.Controls.Add(txtCriteria);
                    htc.VAlign = "middle";
                    htr.Cells.Add(htc);

                    htc = new HtmlTableCell();
                    TextBox txtOr1 = new TextBox();
                    txtOr1.ID = "txt_Or1_" + sColumn_id;
                    txtOr1.CssClass = "controlFont";
                    txtOr1.Width = 100;
                    txtOr1.AutoPostBack = true;
                    txtOr1.TextChanged += new EventHandler(Load_QueryFromExp);
                    htc.Controls.Add(txtOr1);
                    htc.VAlign = "middle";
                    htr.Cells.Add(htc);

                    htc = new HtmlTableCell();
                    TextBox txtOr2 = new TextBox();
                    txtOr2.ID = "txt_Or2_" + sColumn_id;
                    txtOr2.CssClass = "controlFont";
                    txtOr2.Width = 100;
                    txtOr2.AutoPostBack = true;
                    txtOr2.TextChanged += new EventHandler(Load_QueryFromExp);
                    htc.Controls.Add(txtOr2);
                    htc.VAlign = "middle";
                    htr.Cells.Add(htc);

                    htc = new HtmlTableCell();
                    TextBox txtOr3 = new TextBox();
                    txtOr3.ID = "txt_Or3_" + sColumn_id;
                    txtOr3.CssClass = "controlFont";
                    txtOr3.Width = 100;
                    txtOr3.AutoPostBack = true;
                    txtOr3.TextChanged += new EventHandler(Load_QueryFromExp);
                    htc.Controls.Add(txtOr3);
                    htc.VAlign = "middle";
                    htr.Cells.Add(htc);

                    htTable.Rows.Add(htr);
                }
                #endregion
                htTable.Width = "100%";
                pnl_Sql_Expression.Controls.Add(htTable);
                pnl_Sql_Expression.CssClass = "page_tooltip_div";
                lblQExpr.Visible = true;
                pnl_Sql_Expression.Visible = true;
            }
        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in QueryBuilder: getQueryExpression: " + ee.Message);
        }
        finally
        {
            hdd = null;
        }
    }

    /// <summary>
    /// To create a query based on query expression / column selection.
    /// </summary>
    private void getQuery()
    {
        HDDatabase hdd = new HDDatabase();
        string sField = "", sTable = "", sqlFields = "", sAggregate = "", sOrderBy = "";
        try
        {
            if (p_sy_report_sql_list_object.SelectedValue == "none")
                p_sy_report_sql_report_base_sql.Text = "";
            else
            {
                sTable = "[" + hdd.GetColumnValue("select object_table from sy_obj where af_row_id = '" + p_sy_report_sql_list_object.SelectedValue + "'") + "]";
                if (ViewState["Query_Table"] != null && ViewState["Query_Table"].ToString() != "")
                    dtQueryExp = JsonFunctions.JsonFunctions.JsonToDataTable(ViewState["Query_Table"].ToString());
                if (dtQueryExp != null && dtQueryExp.Rows.Count > 0)
                {
                    foreach (DataRow dr in dtQueryExp.Rows)
                    {
                        //sField = sTable + ".[" + dsObjAtt.Tables[0].Select("af_row_id = '" + dr["attrib_id"].ToString() + "'")[0]["attrib_id"].ToString() + "]";
                        sField = dsObjAtt.Tables[0].Select("af_row_id = '" + dr["attrib_id"].ToString() + "'")[0]["attrib_id"].ToString();

                        getAggregateField(ref sField, dr["aggregate"].ToString());

                        if (dr["sort_type"].ToString() != "")
                            sOrderBy += sOrderBy.Length > 0 ? ", " + sField + " " + dr["sort_type"].ToString() : " Order By " + sField + " " + dr["sort_type"].ToString();

                        sField += dr["alias"].ToString() == "" ? "" : " as [" + dr["alias"].ToString() + "]";

                        if (!sqlFields.Contains(sField) && Convert.ToBoolean(dr["output"].ToString()))
                            sqlFields += sqlFields.Length > 0 ? ", " + sField : sField;
                    }
                    getAggregateCondition(dtQueryExp, ref sAggregate, sTable, dsObjAtt);
                }
                txt_sql_builder.Text = "select " + (sqlFields == "" ? "*" : sqlFields) + " from " + sTable + " " + sAggregate + sOrderBy + ";";
                if (p_sy_report_sql_is_user_query.Value == "")
                    p_sy_report_sql_report_base_sql.Text = "select " + (sqlFields == "" ? "*" : sqlFields) + " from " + sTable + " " + sAggregate + sOrderBy + ";";
            }
        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in QueryBuilder: getQuery: " + ee.Message);
        }
        finally
        {
            hdd = null;
        }
    }
    
    /// <summary>
    /// To get the field with aggregate type.
    /// </summary>
    /// <param name="sField">Field</param>
    /// <param name="sAggregate">Aggregate type</param>
    private void getAggregateField(ref string sField, string sAggregate)
    {
        try
        {
            switch (sAggregate.ToLower())
            {
                case "avg":
                    sField = "avg(" + sField + ")";
                    break;
                case "avg distinct":
                    sField = "avg(distinct " + sField + ")";
                    break;
                case "count":
                    sField = "count(" + sField + ")";
                    break;
                case "count distinct":
                    sField = "count(distinct " + sField + ")";
                    break;
                case "max":
                    sField = "max(" + sField + ")";
                    break;
                case "max distinct":
                    sField = "max(distinct " + sField + ")";
                    break;
                case "min":
                    sField = "min(" + sField + ")";
                    break;
                case "min distinct":
                    sField = "min(distinct " + sField + ")";
                    break;
                case "sum":
                    sField = "sum(" + sField + ")";
                    break;
                case "sum distinct":
                    sField = "sum(distinct " + sField + ")";
                    break;
                default:
                    //sField = sTable + ".[" + sField + "]";
                    sField = "[" + sField + "]";
                    break;
            }
        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in QueryBuilder: getAggregateField: " + ee.Message);
        }
    }
    
    /// <summary>
    /// To provide a conditions for the query after the table name.
    /// </summary>
    /// <param name="dtQueryExp">Query Expression table</param>
    /// <param name="sAggregate">Aggregate string</param>
    /// <param name="sTable">Table name</param>
    /// <param name="dsObjAtt">Object attribute dataset</param>
    private void getAggregateCondition(DataTable dtQueryExp, ref string sAggregate, string sTable, DataSet dsObjAtt)
    {
        string sField = "", sWhere = "", sGroupBy = "", sHaving = "";
        string sCriteria = "", sOr1 = "", sOr2 = "", sOr3 = "";
        try
        {
            foreach (DataRow dr in dtQueryExp.Rows)
            {
                if (dr["aggregate"].ToString() != "")
                {
                    sField = dsObjAtt.Tables[0].Select("af_row_id = '" + dr["attrib_id"].ToString() + "'")[0]["attrib_id"].ToString();
                    sCriteria = dr["criteria"].ToString().Replace("'", "''");
                    sOr1 = dr["or1"].ToString().Replace("'", "''");
                    sOr2 = dr["or3"].ToString().Replace("'", "''");
                    sOr3 = dr["or3"].ToString().Replace("'", "''");
                    switch (dr["aggregate"].ToString().ToLower())
                    {
                        case "where":
                            getAggregateField(ref sField, dr["aggregate"].ToString());
                            if (sCriteria != "")
                                sWhere += sWhere.Length > 0 ? " and " + sField + " = '" + sCriteria + "'" : "where " + sField + " = '" + sCriteria + "'";
                            if (sOr1 != "")
                                sWhere += sWhere.Length > 0 ? " or " + sField + " = '" + sOr1 + "'" : "where " + sField + " = '" + sOr1 + "'";
                            if (sOr2 != "")
                                sWhere += sWhere.Length > 0 ? " or " + sField + " = '" + sOr2 + "'" : "where " + sField + " = '" + sOr2 + "'";
                            if (sOr3 != "")
                                sWhere += sWhere.Length > 0 ? " or " + sField + " = '" + sOr3 + "'" : "where " + sField + " = '" + sOr3 + "'";
                            break;
                        case "group by":
                        case "having":
                        case "avg":
                        case "avg distinct":
                        case "count":
                        case "count distinct":
                        case "max":
                        case "max distinct":
                        case "min":
                        case "min distinct":
                        case "sum":
                        case "sum distinct":
                            getAggregateField(ref sField, dr["aggregate"].ToString());
                            //if (dr["aggregate"].ToString() == "group by")
                                sGroupBy += sGroupBy.Length > 0 ? ", " + sField : " group by " + sField;
                            if (sCriteria != "")
                                sHaving += sHaving.Length > 0 ? " and " + sField + " = '" + sCriteria + "'" : " having " + sField + " = '" + sCriteria + "'";
                            if (sOr1 != "")
                                sHaving += sHaving.Length > 0 ? " or " + sField + " = '" + sOr1 + "'" : " having " + sField + " = '" + sOr1 + "'";
                            if (sOr2 != "")
                                sHaving += sHaving.Length > 0 ? " or " + sField + " = '" + sOr2 + "'" : " having " + sField + " = '" + sOr2 + "'";
                            if (sOr3 != "")
                                sHaving += sHaving.Length > 0 ? " or " + sField + " = '" + sOr3 + "'" : " having " + sField + " = '" + sOr3 + "'";
                            break;
                        default:
                            break;
                    }
                }
            }
            sAggregate = sWhere + sGroupBy + sHaving;
        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in QueryBuilder: getAggregateCondition: " + ee.Message);
        }
    }

    /// <summary>
    /// To get the query expression value.
    /// </summary>
    private void getQueryExpTable()
    {
        try
        {
            if (ViewState["Query_Table"] != null)
            {
                dtQueryExp = JsonFunctions.JsonFunctions.JsonToDataTable(ViewState["Query_Table"].ToString());
                foreach (DataRow dr in dtQueryExp.Rows)
                {
                    string sColumn_id = dr["attrib_id"].ToString();
                    CheckBox cbOutput = (CheckBox)frmObj.FindControl("ContentPlaceHolder3").FindControl("chb_output_" + sColumn_id);
                    DropDownList ddlFld = (DropDownList)frmObj.FindControl("ContentPlaceHolder3").FindControl("ddl_Fld_" + sColumn_id);
                    TextBox txtAlias = (TextBox)frmObj.FindControl("ContentPlaceHolder3").FindControl("txt_Alias_" + sColumn_id);
                    DropDownList ddlSortType = (DropDownList)frmObj.FindControl("ContentPlaceHolder3").FindControl("ddl_sort_type_" + sColumn_id);
                    DropDownList ddlExp = (DropDownList)frmObj.FindControl("ContentPlaceHolder3").FindControl("ddl_Exp_" + sColumn_id);
                    TextBox txtCriteria = (TextBox)frmObj.FindControl("ContentPlaceHolder3").FindControl("txt_criteria_" + sColumn_id);
                    TextBox txtOr1 = (TextBox)frmObj.FindControl("ContentPlaceHolder3").FindControl("txt_Or1_" + sColumn_id);
                    TextBox txtOr2 = (TextBox)frmObj.FindControl("ContentPlaceHolder3").FindControl("txt_Or2_" + sColumn_id);
                    TextBox txtOr3 = (TextBox)frmObj.FindControl("ContentPlaceHolder3").FindControl("txt_Or3_" + sColumn_id);

                    dr["object_id"] = p_sy_report_sql_list_object.SelectedValue;
                    dr["attrib_id"] = ddlFld.SelectedValue;
                    dr["output"] = cbOutput.Checked.ToString();
                    dr["alias"] = txtAlias.Text;
                    dr["sort_type"] = ddlSortType.SelectedValue;
                    dr["aggregate"] = ddlExp.SelectedValue;
                    dr["criteria"] = txtCriteria.Text;
                    dr["or1"] = txtOr1.Text;
                    dr["or2"] = txtOr2.Text;
                    dr["or3"] = txtOr3.Text;
                }
                ViewState["Query_Table"] = new JavaScriptSerializer().Serialize(JsonFunctions.JsonFunctions.ToJson(dtQueryExp));
            }
        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in QueryBuilder: getQueryExpTable: " + ee.Message);
        }
    }

    private void getDefaultObj()
    {
        try
        {
            //p_sy_report_sql_list_object.Items[1].Selected = true;
            //getColNames();
            //if (cblColumns.Items.Count > 1)
            //{
            //    cblColumns.Items[0].Selected = true;
            //    cblColumns.Items[1].Selected = true;
            //}
            //getColNames();
        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in QueryBuilder: getDefaultObj: " + ee.Message);
        }
    }

    protected void Load_QueryFromExp(object sender, EventArgs e)
    {
        p_sy_report_sql_is_user_query.Value = "";
        getQueryExpTable();
        getQuery();
    }

    protected void Load_QueryFromExp_Agg(object sender, EventArgs e)
    {
        p_sy_report_sql_is_user_query.Value = "";
        DropDownList ddlAggre = ((DropDownList)(sender));
        if (ddlAggre.SelectedValue == "group by" || ddlAggre.SelectedValue == "having")
        {
            foreach (DataRow drAgg in dtQueryExp.Select("aggregate in ('where', '') or aggregate is null"))
            {
                DropDownList ddlExp = (DropDownList)frmObj.FindControl("ContentPlaceHolder3").FindControl("ddl_Exp_" + drAgg["attrib_id"].ToString());
                if (ddlExp.ID != ddlAggre.ID)
                {
                    ddlExp.SelectedValue = "group by";
                    drAgg["aggregate"] = "group by";
                }
            }
        }
        getQueryExpTable();
        getQuery();
    }

    /// <summary>
    /// To get whether it is system based query or user based query.
    /// </summary>
    private void checkSystemQuery()
    {
        try
        {
            if (txt_sql_builder.Text != p_sy_report_sql_report_base_sql.Text)
                p_sy_report_sql_is_system_query.Checked = false;
            else
                p_sy_report_sql_is_system_query.Checked = true;
        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in QueryBuilder: checkSystemQuery: " + ee.Message);
        }
    }

    /// <summary>
    /// To load the Query builder with related columns.
    /// </summary>
    private void loadQueryBuilder()
    {
        HDDatabase hdd = new HDDatabase();
        try
        {
            dsObjAtt = hdd.GetDataset("select af_row_id, attrib_id, attrib_seq from sy_obj_att where object_id='" + p_sy_report_sql_list_object.SelectedValue + "';");
            cblColumns.Items.Clear();
            foreach (DataRow dr in dsObjAtt.Tables[0].Select("", "attrib_seq asc"))
            {
                ListItem liObjAtt = new ListItem(dr["attrib_id"].ToString(), dr["af_row_id"].ToString());
                cblColumns.Items.Add(liObjAtt);
            }
            //lblColNames.Text = "Select the fields:";
            lblColNames.Visible = true;
            cblColumns.Enabled = true;
            pnlChbxColumn.Visible = true;
            pnlChbxColumn.CssClass = "page_tooltip_div";

            if (Request.QueryString["PK"] != null)
            {
                dtQueryExp = hdd.GetDataTable("select * from sy_report_sql_setting where query_id = '" + Request.QueryString["PK"].ToString() + "' and object_id = '" + p_sy_report_sql_list_object.SelectedValue + "';");
                if (dtQueryExp.Rows.Count > 0)
                {
                    foreach (DataRow dr in dtQueryExp.Rows)
                    {
                        cblColumns.Items.FindByValue(dr["attrib_id"].ToString()).Selected = true;
                    }
                    getQueryExpression();
                    loadQueryExpTable();
                    ViewState["Query_Table"] = new JavaScriptSerializer().Serialize(JsonFunctions.JsonFunctions.ToJson(dtQueryExp));
                    
                }
            }
        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in QueryBuilder: loadQueryBuilder: " + ee.Message);
        }
    }

    /// <summary>
    /// To load query expression table with related values.
    /// </summary>
    private void loadQueryExpTable()
    {
        try
        {
            foreach (DataRow dr in dtQueryExp.Rows)
            {
                CheckBox cbOutput = (CheckBox)frmObj.FindControl("ContentPlaceHolder3").FindControl("chb_output_" + dr["attrib_id"].ToString());
                DropDownList ddlFld = (DropDownList)frmObj.FindControl("ContentPlaceHolder3").FindControl("ddl_Fld_" + dr["attrib_id"].ToString());
                TextBox txtAlias = (TextBox)frmObj.FindControl("ContentPlaceHolder3").FindControl("txt_Alias_" + dr["attrib_id"].ToString());
                DropDownList ddlSortType = (DropDownList)frmObj.FindControl("ContentPlaceHolder3").FindControl("ddl_sort_type_" + dr["attrib_id"].ToString());
                DropDownList ddlExp = (DropDownList)frmObj.FindControl("ContentPlaceHolder3").FindControl("ddl_Exp_" + dr["attrib_id"].ToString());
                TextBox txtCriteria = (TextBox)frmObj.FindControl("ContentPlaceHolder3").FindControl("txt_criteria_" + dr["attrib_id"].ToString());
                TextBox txtOr1 = (TextBox)frmObj.FindControl("ContentPlaceHolder3").FindControl("txt_Or1_" + dr["attrib_id"].ToString());
                TextBox txtOr2 = (TextBox)frmObj.FindControl("ContentPlaceHolder3").FindControl("txt_Or2_" + dr["attrib_id"].ToString());
                TextBox txtOr3 = (TextBox)frmObj.FindControl("ContentPlaceHolder3").FindControl("txt_Or3_" + dr["attrib_id"].ToString());
                cbOutput.Checked = Convert.ToBoolean(dr["output"].ToString());
                //ddlFld.SelectedValue = dr["attrib_id"].ToString();
                txtAlias.Text = dr["alias"].ToString();
                ddlSortType.SelectedValue = dr["sort_type"].ToString();
                ddlExp.SelectedValue = dr["aggregate"].ToString();
                txtCriteria.Text = dr["criteria"].ToString();
                txtOr1.Text = dr["or1"].ToString();
                txtOr2.Text = dr["or2"].ToString();
                txtOr3.Text = dr["or3"].ToString();
            }
        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in QueryBuilder: loadQueryExpTable: " + ee.Message);
        }
    }

    /// <summary>
    /// To save the query builder with related query id.
    /// </summary>
    private void SaveQueryBuild()
    {
        Master MA = new Master();
        ScreenReader sr = new ScreenReader();
        HDDatabase hdd = new HDDatabase();
        DataSet dseChildPageInfo = new DataSet();
        DataSet dseChildInputInfo = new DataSet();
        DataTable dtTemp = new DataTable();
        DataTable dtUpdate = new DataTable();
        bool isCopy = false, blnStatus = false;
        string sDelKeys = "";
        try
        {
            if (ViewState["Query_Table"] != null && ViewState["Query_Table"].ToString() != "")
                dtQueryExp = JsonFunctions.JsonFunctions.JsonToDataTable(ViewState["Query_Table"].ToString());
            if (dtQueryExp != null && dtQueryExp.Rows.Count > 0)
            {
                if (p_sy_report_sql_is_system_query.Checked)
                {
                    if (Request.QueryString["CPY"] != null && Request.QueryString["CPY"] == "0")
                        isCopy = true;

                    PageInfoLoad PIL = new PageInfoLoad();
                    dseChildPageInfo = PIL.GetPageSetPageCtrlInfo("a7cd263f2426a1f5"); // report_sql_setting

                    DataColumn dcObjectId = dtQueryExp.Columns["object_id"];
                    dcObjectId.Expression = "\'" + dseInputInfo.Tables[0].Rows[0]["primary_object_id"].ToString() + "\'";
                    DataColumn dcQueryId = dtQueryExp.Columns["query_id"];
                    dcQueryId.Expression = "\'" + dseInputInfo.Tables[0].Rows[0]["af_row_id"].ToString() + "\'";

                    dtQueryExp.TableName = "p_report_sql_setting";

                    dtTemp = hdd.GetDataTable("select * from sy_report_sql_setting where query_id = '" + dseInputInfo.Tables[0].Rows[0]["af_row_id"].ToString() + "'");
                    if (dtTemp.Rows.Count == 0 || isCopy)
                    {
                        DataColumn dcOwnerId = dtQueryExp.Columns["owner_id"];
                        dcOwnerId.Expression = "\'" + HttpContext.Current.Session["user_afrowid"].ToString() + "\'";
                        if (isCopy)
                        {
                            DataColumn dcAfRowId = dtQueryExp.Columns["af_row_id"];
                            dcAfRowId.Expression = "";
                        }
                        dseChildInputInfo.Tables.Add(dtQueryExp.Copy());
                        blnStatus = MA.ProcessOperation(dseChildPageInfo, dseChildInputInfo, "INSERT");
                    }
                    else
                    {
                        dtUpdate = dtQueryExp.Clone();
                        foreach (DataRow dr in dtTemp.Rows)
                        {
                            DataRow[] drExist = dtQueryExp.Select("af_row_id = '" + dr["af_row_id"].ToString() + "'");
                            if (drExist.Length == 0)
                                sDelKeys += "'" + dr["af_row_id"].ToString() + "',";
                            else
                            {
                                if (drExist[0]["owner_id"].ToString() == "")
                                    drExist[0]["owner_id"] = HttpContext.Current.Session["user_afrowid"].ToString();
                                dtUpdate.ImportRow(drExist[0]);
                                dtQueryExp.Rows.Remove(drExist[0]);
                            }
                        }
                        DataColumn dcOwnerId = dtQueryExp.Columns["owner_id"];
                        dcOwnerId.Expression = "\'" + HttpContext.Current.Session["user_afrowid"].ToString() + "\'";
                        dseChildInputInfo.Tables.Add(dtQueryExp.Copy());
                        blnStatus = MA.ProcessOperation(dseChildPageInfo, dseChildInputInfo, "INSERT");
                        dseChildInputInfo = new DataSet();
                        dseChildInputInfo.Tables.Add(dtUpdate.Copy());
                        blnStatus = MA.ProcessOperation(dseChildPageInfo, dseChildInputInfo, "UPDATE");
                        if (sDelKeys != "")
                        {
                            SqlCommand sql = new SqlCommand("delete from sy_report_sql_setting where af_row_id in (" + sDelKeys.TrimEnd(',') + ");");
                            int executed = hdd.ExecuteCmdObj(sql, true);
                        }
                    }
                }
                else
                {
                    foreach (DataRow dr in dtQueryExp.Rows)
                    {
                        sDelKeys += "'" + dr["af_row_id"].ToString() + "',";
                    }
                    if (sDelKeys != "")
                    {
                        SqlCommand sql = new SqlCommand("delete from sy_report_sql_setting where af_row_id in (" + sDelKeys.TrimEnd(',') + ");");
                        int executed = hdd.ExecuteCmdObj(sql, true);
                    }
                }
            }
            else if (Request.QueryString["PK"] != null)
            {
                SqlCommand sql = new SqlCommand("delete from sy_report_sql_setting where query_id = '" + Request.QueryString["PK"].ToString() + "';");
                int executed = hdd.ExecuteCmdObj(sql, true);
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error: RoleSecurity: SaveQueryBuild() : " + ex.Message);
        }
        finally
        {
            sr = null; hdd = null;
            if (dseChildPageInfo != null)
                dseChildPageInfo.Dispose();
            if (dseChildInputInfo != null)
                dseChildInputInfo.Dispose();
            if (dtTemp != null)
                dtTemp.Dispose();
            if (dtUpdate != null)
                dtUpdate.Dispose();
        }
    }

    /// <summary>
    /// To include the sequence of parameter, if not exist.
    /// </summary>
    /// <param name="sqlParam">Parameter without sequence</param>
    /// <returns>Parameter with sequence of [1].</returns>
    // Added by 'Imran Gani' on 28-Oct-2013, to include the parameter sequence for older records.
    private string checkParameterSequence(string sqlParam)
    {
        string[] sChk = { "][" };
        try
        {
            if (sqlParam.Trim() == "") return sqlParam;
            if (sqlParam.ToLower().StartsWith("parameters "))
                sqlParam = sqlParam.Replace("parameters ", "");
            else if (sqlParam.ToLower().StartsWith("parameters"))
                sqlParam = sqlParam.Replace("parameters", "");
            foreach (string sParam in sqlParam.Split(','))
            {
                if (sParam.Split(sChk, StringSplitOptions.None).Length <= 2)
                    sqlParam = sqlParam.Replace(sParam.Trim(), "[1]" + sParam.Trim());
            }
            if (!sqlParam.Trim().ToLower().StartsWith("parameters") && sqlParam.Trim() != "")
                sqlParam = "parameters " + sqlParam;
        }
        catch (Exception ee)
        {
            LogWriter.WriteLog("Error in QueryBuilder: checkParameterSequence: " + ee.Message);
        }
        return sqlParam;
    }

    /// <summary>
    /// To get parameter from new syntax with sequence.
    /// </summary>
    /// <param name="sParam">String parameter</param>
    /// <returns>String parameter without sequence</returns>
    // Added by 'Imran Gani' on 28-Oct-2013, to get parameter.
    private string getParameterKey(string sParam)
    {
        string sReturn = sParam;
        try
        {
            string[] sChk = { "][" };
            if (sParam.Split(sChk, StringSplitOptions.None).Length > 2)
                sReturn = sParam.Substring(sParam.IndexOf("][") + 1);
        }
        catch (Exception e)
        {
            LogWriter.WriteLog("Error in QueryBuilder : getParameterKey: " + e.Message);
        }
        return sReturn;
    }
}
